library(here)
library(readxl)
library(dplyr)
library(BelgiumMaps.StatBel)
library(sf)
library(tmap)
library(leaflet)
library(leaflet.minicharts)
library(htmltools)
library(ggplot2)
library(stringr)
dynam.prov = read_excel(
    '20180425_dynam_be_jobdata_origineel.xlsx', 'Sheet2',
    skip = 1,  col_names = c(
      'province_lbl', 'province_nis_code', 
      'absoluut_bruto_toename', 'absoluut_bruto_afname', 'absoluut_netto', 'delme',
      'delmetoo', 'bruto_toename_pct', 'bruto_afname_pct', 'netto_pct')) %>%
  select(-delme, -delmetoo)
dynam.prov
dynam.prov  <- dynam.prov %>% 
  mutate(
    province_lbl = case_when(
      province_lbl == 'Brussels Hoofdstedelijk Gewest' ~ 'Brussel',
      TRUE ~ province_lbl))
dynam.prov = dynam.prov %>%
  mutate(
    province_lbl_pct = str_c(province_lbl, '  (', str_sub(as.character(netto_pct*100),1,4), '%', ')'))
province_lbl_ordered <- dynam.prov %>%
  arrange(netto_pct) %>% # rangschik procentuele of absolute groei
  # arrange(absoluut_netto) %>%
  pull(province_lbl_pct)
    
dynam.prov = dynam.prov %>%
    mutate(province_lbl_pct = factor(province_lbl_pct, levels = province_lbl_ordered))
# Overlapping barcharts:
# http://stephanieevergreen.com/overlapping-bars/
# https://gist.github.com/hrbrmstr/035f998517de2384e9962cff7df874bd
# 
gg <- ggplot(data=dynam.prov, aes(y=province_lbl_pct, yend=province_lbl_pct))
gg <- gg + geom_segment(aes(x=absoluut_bruto_afname, xend=0, color="Jobverlies"), size=10)
gg <- gg + geom_segment(aes(x=absoluut_bruto_toename, xend=0, color="Jobgroei"), size=5)
gg <- gg + scale_x_continuous(labels=function(x) format(x, decimal.mark = ',', big.mark = ".", scientific = FALSE))
gg <- gg + scale_color_manual(name=NULL, values=c(Jobverlies="#bebebf", Jobgroei="#1074bc"))
gg <- gg + guides(color=guide_legend(keywidth=0, override.aes=list(size=4)))
gg <- gg + labs(
  x=NULL, y=NULL,
  title="Alle provincies kennen een netto jobaangroei, met de grootste\nprocentuele groei in Namen",
  subtitle = 'Procentuele en absolute jobevolutie per provincie, 2016-2017',
  caption="Bron: HIVA-KU Leuven | DynaM | dynamresearch.be")
gg <- gg + theme_minimal()
gg <- gg + theme(axis.text.x=element_text(margin=margin(t=0)))
gg <- gg + theme(axis.text.y=element_text(margin=margin(r=-10)))
gg <- gg + theme(panel.grid.minor=element_blank())
gg <- gg + theme(panel.grid.major.y=element_blank())
gg <- gg + theme(plot.title=element_text(face="bold"))
gg <- gg + theme(plot.margin=margin(20,20,20,20))
gg <- gg + theme(plot.caption=element_text(size=8, margin=margin(t=10, r=0)))
gg <- gg + theme(legend.position=c(0.8, 0.9))
gg <- gg + theme(legend.direction="vertical")
gg <- gg + theme(legend.background=element_rect(fill="white", color="white"))
gg

dynam.prov = dynam.prov %>%
  mutate(
    netto_pct = netto_pct*100,
    absoluut_bruto_afname = absoluut_bruto_afname*-1)
data('BE_ADMIN_PROVINCE') 
data("BE_ADMIN_REGION")
# convert to simple features dataset-structure
provinces = st_as_sf(BE_ADMIN_PROVINCE) 
regions = st_as_sf(BE_ADMIN_REGION) 
# Spatial object for Brussels-region is not included in provences, add from region
provinces = rbind(
  provinces %>% select(CD_PROV_REFNIS, TX_PROV_DESCR_NL),
  regions %>%
    filter(TX_RGN_DESCR_NL == "Brussels Hoofdstedelijk Gewest") %>%
    mutate(CD_RGN_REFNIS = '04000') %>%
    select(
      CD_PROV_REFNIS = CD_RGN_REFNIS,
      TX_PROV_DESCR_NL = TX_RGN_DESCR_NL))
provinces = provinces %>%
  mutate(CD_PROV_REFNIS = as.character(CD_PROV_REFNIS)) %>%
  left_join(dynam.prov, c('CD_PROV_REFNIS' = 'province_nis_code'))
pal <- colorBin("Greens", domain = dynam.prov$netto_pct)
# get centroid coordinates for each province to plot barchart there
provinces_coords = st_coordinates(st_centroid(provinces))
d.abs = dynam.prov %>%
  select(
    'Jobgroei' = absoluut_bruto_toename,
    'Jobverlies' = absoluut_bruto_afname,
    'Netto jobevolutie' = absoluut_netto)
m.prov = leaflet(provinces, width = '100%') %>% 
  # add grey arrondissement polygons w/t white border
  addPolygons(
    weight = 2,
    opacity = 1,
    dashArray = "3",
    fillColor = ~pal(netto_pct), color = 'white') %>% 
  addLegend(pal = pal, values = ~absoluut_netto, opacity = 0.7,
            title = 'Netto jobevolutie (%)',
  position = "bottomright")
colors <- c("#7cae00", "#f8766d", "#c77cff")
m.prov = m.prov %>%
  addMinicharts(
    provinces_coords[,1], provinces_coords[,2],
    chartdata = d.abs,
    colorPalette = colors,
    width = 45, height = 45)
map_title <- tags$div(
   HTML('<b>Bruto en netto jobevolutie per provincie, 2017-2018 (<a href="https://dynamresearch.be/">DynaM</a>)</b>')
 )  
m.prov = m.prov %>% 
  addControl(map_title, position = "bottomleft")
m.prov
LS0tDQp0aXRsZTogIkR5bmFtIGpvYmV2b2x1dGllIGthYXJ0amVzIg0Kb3V0cHV0OiANCiAgaHRtbF9ub3RlYm9vazogDQogICAgY29kZV9mb2xkaW5nOiBzaG93DQogICAgdGhlbWU6IGx1bWVuDQotLS0NCg0KYGBge3IsIG1lc3NhZ2U9RkFMU0UsIHdhcm5pbmc9RkFMU0V9DQpsaWJyYXJ5KGhlcmUpDQpsaWJyYXJ5KHJlYWR4bCkNCmxpYnJhcnkoZHBseXIpDQpsaWJyYXJ5KEJlbGdpdW1NYXBzLlN0YXRCZWwpDQpsaWJyYXJ5KHNmKQ0KbGlicmFyeSh0bWFwKQ0KbGlicmFyeShsZWFmbGV0KQ0KbGlicmFyeShsZWFmbGV0Lm1pbmljaGFydHMpDQpsaWJyYXJ5KGh0bWx0b29scykNCmxpYnJhcnkoZ2dwbG90MikNCmxpYnJhcnkoc3RyaW5ncikNCmBgYA0KDQpgYGB7cn0NCmR5bmFtLnByb3YgPSByZWFkX2V4Y2VsKA0KICAgICcyMDE4MDQyNV9keW5hbV9iZV9qb2JkYXRhX29yaWdpbmVlbC54bHN4JywgJ1NoZWV0MicsDQogICAgc2tpcCA9IDEsICBjb2xfbmFtZXMgPSBjKA0KICAgICAgJ3Byb3ZpbmNlX2xibCcsICdwcm92aW5jZV9uaXNfY29kZScsIA0KICAgICAgJ2Fic29sdXV0X2JydXRvX3RvZW5hbWUnLCAnYWJzb2x1dXRfYnJ1dG9fYWZuYW1lJywgJ2Fic29sdXV0X25ldHRvJywgJ2RlbG1lJywNCiAgICAgICdkZWxtZXRvbycsICdicnV0b190b2VuYW1lX3BjdCcsICdicnV0b19hZm5hbWVfcGN0JywgJ25ldHRvX3BjdCcpKSAlPiUNCiAgc2VsZWN0KC1kZWxtZSwgLWRlbG1ldG9vKQ0KYGBgDQoNCmBgYHtyfQ0KZHluYW0ucHJvdg0KYGBgDQoNCmBgYHtyLCB3YXJuaW5nPUZBTFNFLCBtZXNzYWdlPUZBTFNFfQ0KZHluYW0ucHJvdiAgPC0gZHluYW0ucHJvdiAlPiUgDQogIG11dGF0ZSgNCiAgICBwcm92aW5jZV9sYmwgPSBjYXNlX3doZW4oDQogICAgICBwcm92aW5jZV9sYmwgPT0gJ0JydXNzZWxzIEhvb2Zkc3RlZGVsaWprIEdld2VzdCcgfiAnQnJ1c3NlbCcsDQogICAgICBUUlVFIH4gcHJvdmluY2VfbGJsKSkNCg0KZHluYW0ucHJvdiA9IGR5bmFtLnByb3YgJT4lDQogIG11dGF0ZSgNCiAgICBwcm92aW5jZV9sYmxfcGN0ID0gc3RyX2MocHJvdmluY2VfbGJsLCAnICAoJywgc3RyX3N1Yihhcy5jaGFyYWN0ZXIobmV0dG9fcGN0KjEwMCksMSw0KSwgJyUnLCAnKScpKQ0KDQpwcm92aW5jZV9sYmxfb3JkZXJlZCA8LSBkeW5hbS5wcm92ICU+JQ0KICBhcnJhbmdlKG5ldHRvX3BjdCkgJT4lICMgcmFuZ3NjaGlrIHByb2NlbnR1ZWxlIG9mIGFic29sdXRlIGdyb2VpDQogICMgYXJyYW5nZShhYnNvbHV1dF9uZXR0bykgJT4lDQogIHB1bGwocHJvdmluY2VfbGJsX3BjdCkNCg0KICAgIA0KZHluYW0ucHJvdiA9IGR5bmFtLnByb3YgJT4lDQogICAgbXV0YXRlKHByb3ZpbmNlX2xibF9wY3QgPSBmYWN0b3IocHJvdmluY2VfbGJsX3BjdCwgbGV2ZWxzID0gcHJvdmluY2VfbGJsX29yZGVyZWQpKQ0KDQoNCmBgYA0KDQpgYGB7ciwgZmlnLndpZHRoPTgsIGZpZy5oZWlnaHQ9NiwgZmlnLmFsaWduPSdjZW50ZXInfQ0KIyBPdmVybGFwcGluZyBiYXJjaGFydHM6DQojIGh0dHA6Ly9zdGVwaGFuaWVldmVyZ3JlZW4uY29tL292ZXJsYXBwaW5nLWJhcnMvDQojIGh0dHBzOi8vZ2lzdC5naXRodWIuY29tL2hyYnJtc3RyLzAzNWY5OTg1MTdkZTIzODRlOTk2MmNmZjdkZjg3NGJkDQojIA0KDQpnZyA8LSBnZ3Bsb3QoZGF0YT1keW5hbS5wcm92LCBhZXMoeT1wcm92aW5jZV9sYmxfcGN0LCB5ZW5kPXByb3ZpbmNlX2xibF9wY3QpKQ0KZ2cgPC0gZ2cgKyBnZW9tX3NlZ21lbnQoYWVzKHg9YWJzb2x1dXRfYnJ1dG9fYWZuYW1lLCB4ZW5kPTAsIGNvbG9yPSJKb2J2ZXJsaWVzIiksIHNpemU9MTApDQpnZyA8LSBnZyArIGdlb21fc2VnbWVudChhZXMoeD1hYnNvbHV1dF9icnV0b190b2VuYW1lLCB4ZW5kPTAsIGNvbG9yPSJKb2Jncm9laSIpLCBzaXplPTUpDQpnZyA8LSBnZyArIHNjYWxlX3hfY29udGludW91cyhsYWJlbHM9ZnVuY3Rpb24oeCkgZm9ybWF0KHgsIGRlY2ltYWwubWFyayA9ICcsJywgYmlnLm1hcmsgPSAiLiIsIHNjaWVudGlmaWMgPSBGQUxTRSkpDQpnZyA8LSBnZyArIHNjYWxlX2NvbG9yX21hbnVhbChuYW1lPU5VTEwsIHZhbHVlcz1jKEpvYnZlcmxpZXM9IiNiZWJlYmYiLCBKb2Jncm9laT0iIzEwNzRiYyIpKQ0KZ2cgPC0gZ2cgKyBndWlkZXMoY29sb3I9Z3VpZGVfbGVnZW5kKGtleXdpZHRoPTAsIG92ZXJyaWRlLmFlcz1saXN0KHNpemU9NCkpKQ0KZ2cgPC0gZ2cgKyBsYWJzKA0KICB4PU5VTEwsIHk9TlVMTCwNCiAgdGl0bGU9IkFsbGUgcHJvdmluY2llcyBrZW5uZW4gZWVuIG5ldHRvIGpvYmFhbmdyb2VpLCBtZXQgZGUgZ3Jvb3RzdGVcbnByb2NlbnR1ZWxlIGdyb2VpIGluIE5hbWVuIiwNCiAgc3VidGl0bGUgPSAnUHJvY2VudHVlbGUgZW4gYWJzb2x1dGUgam9iZXZvbHV0aWUgcGVyIHByb3ZpbmNpZSwgMjAxNi0yMDE3JywNCiAgY2FwdGlvbj0iQnJvbjogSElWQS1LVSBMZXV2ZW4gfCBEeW5hTSB8IGR5bmFtcmVzZWFyY2guYmUiKQ0KZ2cgPC0gZ2cgKyB0aGVtZV9taW5pbWFsKCkNCmdnIDwtIGdnICsgdGhlbWUoYXhpcy50ZXh0Lng9ZWxlbWVudF90ZXh0KG1hcmdpbj1tYXJnaW4odD0wKSkpDQpnZyA8LSBnZyArIHRoZW1lKGF4aXMudGV4dC55PWVsZW1lbnRfdGV4dChtYXJnaW49bWFyZ2luKHI9LTEwKSkpDQpnZyA8LSBnZyArIHRoZW1lKHBhbmVsLmdyaWQubWlub3I9ZWxlbWVudF9ibGFuaygpKQ0KZ2cgPC0gZ2cgKyB0aGVtZShwYW5lbC5ncmlkLm1ham9yLnk9ZWxlbWVudF9ibGFuaygpKQ0KZ2cgPC0gZ2cgKyB0aGVtZShwbG90LnRpdGxlPWVsZW1lbnRfdGV4dChmYWNlPSJib2xkIikpDQpnZyA8LSBnZyArIHRoZW1lKHBsb3QubWFyZ2luPW1hcmdpbigyMCwyMCwyMCwyMCkpDQpnZyA8LSBnZyArIHRoZW1lKHBsb3QuY2FwdGlvbj1lbGVtZW50X3RleHQoc2l6ZT04LCBtYXJnaW49bWFyZ2luKHQ9MTAsIHI9MCkpKQ0KZ2cgPC0gZ2cgKyB0aGVtZShsZWdlbmQucG9zaXRpb249YygwLjgsIDAuOSkpDQpnZyA8LSBnZyArIHRoZW1lKGxlZ2VuZC5kaXJlY3Rpb249InZlcnRpY2FsIikNCmdnIDwtIGdnICsgdGhlbWUobGVnZW5kLmJhY2tncm91bmQ9ZWxlbWVudF9yZWN0KGZpbGw9IndoaXRlIiwgY29sb3I9IndoaXRlIikpDQpnZw0KYGBgDQoNCg0KYGBge3IsIHdhcm5pbmc9RkFMU0V9DQpkeW5hbS5wcm92ID0gZHluYW0ucHJvdiAlPiUNCiAgbXV0YXRlKA0KICAgIG5ldHRvX3BjdCA9IG5ldHRvX3BjdCoxMDAsDQogICAgYWJzb2x1dXRfYnJ1dG9fYWZuYW1lID0gYWJzb2x1dXRfYnJ1dG9fYWZuYW1lKi0xKQ0KYGBgDQoNCmBgYHtyfQ0KZGF0YSgnQkVfQURNSU5fUFJPVklOQ0UnKSANCmRhdGEoIkJFX0FETUlOX1JFR0lPTiIpDQoNCiMgY29udmVydCB0byBzaW1wbGUgZmVhdHVyZXMgZGF0YXNldC1zdHJ1Y3R1cmUNCnByb3ZpbmNlcyA9IHN0X2FzX3NmKEJFX0FETUlOX1BST1ZJTkNFKSANCnJlZ2lvbnMgPSBzdF9hc19zZihCRV9BRE1JTl9SRUdJT04pIA0KDQojIFNwYXRpYWwgb2JqZWN0IGZvciBCcnVzc2Vscy1yZWdpb24gaXMgbm90IGluY2x1ZGVkIGluIHByb3ZlbmNlcywgYWRkIGZyb20gcmVnaW9uDQpwcm92aW5jZXMgPSByYmluZCgNCiAgcHJvdmluY2VzICU+JSBzZWxlY3QoQ0RfUFJPVl9SRUZOSVMsIFRYX1BST1ZfREVTQ1JfTkwpLA0KICByZWdpb25zICU+JQ0KICAgIGZpbHRlcihUWF9SR05fREVTQ1JfTkwgPT0gIkJydXNzZWxzIEhvb2Zkc3RlZGVsaWprIEdld2VzdCIpICU+JQ0KICAgIG11dGF0ZShDRF9SR05fUkVGTklTID0gJzA0MDAwJykgJT4lDQogICAgc2VsZWN0KA0KICAgICAgQ0RfUFJPVl9SRUZOSVMgPSBDRF9SR05fUkVGTklTLA0KICAgICAgVFhfUFJPVl9ERVNDUl9OTCA9IFRYX1JHTl9ERVNDUl9OTCkpDQpgYGANCg0KYGBge3J9DQpwcm92aW5jZXMgPSBwcm92aW5jZXMgJT4lDQogIG11dGF0ZShDRF9QUk9WX1JFRk5JUyA9IGFzLmNoYXJhY3RlcihDRF9QUk9WX1JFRk5JUykpICU+JQ0KICBsZWZ0X2pvaW4oZHluYW0ucHJvdiwgYygnQ0RfUFJPVl9SRUZOSVMnID0gJ3Byb3ZpbmNlX25pc19jb2RlJykpDQpgYGANCg0KDQpgYGB7cn0NCnBhbCA8LSBjb2xvckJpbigiR3JlZW5zIiwgZG9tYWluID0gZHluYW0ucHJvdiRuZXR0b19wY3QpDQpgYGANCg0KYGBge3IsIHdhcm5pbmc9RkFMU0V9DQojIGdldCBjZW50cm9pZCBjb29yZGluYXRlcyBmb3IgZWFjaCBwcm92aW5jZSB0byBwbG90IGJhcmNoYXJ0IHRoZXJlDQpwcm92aW5jZXNfY29vcmRzID0gc3RfY29vcmRpbmF0ZXMoc3RfY2VudHJvaWQocHJvdmluY2VzKSkNCmBgYA0KDQpgYGB7cn0NCmQuYWJzID0gZHluYW0ucHJvdiAlPiUNCiAgc2VsZWN0KA0KICAgICdKb2Jncm9laScgPSBhYnNvbHV1dF9icnV0b190b2VuYW1lLA0KICAgICdKb2J2ZXJsaWVzJyA9IGFic29sdXV0X2JydXRvX2FmbmFtZSwNCiAgICAnTmV0dG8gam9iZXZvbHV0aWUnID0gYWJzb2x1dXRfbmV0dG8pDQpgYGANCg0KDQpgYGB7ciByZXN1bHRzPSJhc2lzIiwgZWNobz1GQUxTRX0NCmNhdCgiDQo8c3R5bGU+DQoubGVhZmxldC1jb250YWluZXIgew0KICAgIGJhY2tncm91bmQ6ICNGRkY7DQp9DQo8L3N0eWxlPg0KIikNCmBgYA0KDQpgYGB7cn0NCm0ucHJvdiA9IGxlYWZsZXQocHJvdmluY2VzLCB3aWR0aCA9ICcxMDAlJykgJT4lIA0KICAjIGFkZCBncmV5IGFycm9uZGlzc2VtZW50IHBvbHlnb25zIHcvdCB3aGl0ZSBib3JkZXINCiAgYWRkUG9seWdvbnMoDQogICAgd2VpZ2h0ID0gMiwNCiAgICBvcGFjaXR5ID0gMSwNCiAgICBkYXNoQXJyYXkgPSAiMyIsDQogICAgZmlsbENvbG9yID0gfnBhbChuZXR0b19wY3QpLCBjb2xvciA9ICd3aGl0ZScpICU+JSANCiAgYWRkTGVnZW5kKHBhbCA9IHBhbCwgdmFsdWVzID0gfmFic29sdXV0X25ldHRvLCBvcGFjaXR5ID0gMC43LA0KICAgICAgICAgICAgdGl0bGUgPSAnTmV0dG8gam9iZXZvbHV0aWUgKCUpJywNCiAgcG9zaXRpb24gPSAiYm90dG9tcmlnaHQiKQ0KDQpjb2xvcnMgPC0gYygiIzdjYWUwMCIsICIjZjg3NjZkIiwgIiNjNzdjZmYiKQ0KbS5wcm92ID0gbS5wcm92ICU+JQ0KICBhZGRNaW5pY2hhcnRzKA0KICAgIHByb3ZpbmNlc19jb29yZHNbLDFdLCBwcm92aW5jZXNfY29vcmRzWywyXSwNCiAgICBjaGFydGRhdGEgPSBkLmFicywNCiAgICBjb2xvclBhbGV0dGUgPSBjb2xvcnMsDQogICAgd2lkdGggPSA0NSwgaGVpZ2h0ID0gNDUpDQoNCm1hcF90aXRsZSA8LSB0YWdzJGRpdigNCiAgIEhUTUwoJzxiPkJydXRvIGVuIG5ldHRvIGpvYmV2b2x1dGllIHBlciBwcm92aW5jaWUsIDIwMTctMjAxOCAoPGEgaHJlZj0iaHR0cHM6Ly9keW5hbXJlc2VhcmNoLmJlLyI+RHluYU08L2E+KTwvYj4nKQ0KICkgIA0KDQptLnByb3YgPSBtLnByb3YgJT4lIA0KICBhZGRDb250cm9sKG1hcF90aXRsZSwgcG9zaXRpb24gPSAiYm90dG9tbGVmdCIpDQoNCm0ucHJvdg0KDQpgYGANCg0K